package org.lq.user.dao.impl;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.lq.user.dao.UsersDao;
import org.lq.user.entity.Users;
import org.lq.user.util.DBUtil;

public class UsersDaoImpl implements UsersDao {

	QueryRunner qr = new QueryRunner(DBUtil.getDataSource());
	
	@Override
	public int saveUser(Users u) {
		String sql =
				"insert into users" +
						"  (id, name, username, password, gender, birthday, icon)" + 
						"values" + 
						"  (users_seq.nextval, ?, ?, ?, ?, ?, ?)";
		int num = 0;
		try {
			num = qr.execute(sql, u.getName(),u.getUserName(),u.getPassword(),u.getGender(),u.getBirthday(),u.getIcon());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}

	@Override
	public int updateUser(Users u) {
		int num = 0;
		String sql =
				"update users" +
						"   set" + 
						"       name = ?," + 
						"       username = ?," + 
						"       password = ?," + 
						"       gender = ?," + 
						"       birthday = ?," + 
						"       icon = ?" + 
						" where id = ?;";

		try {
			num = qr.execute(sql, u.getName(),u.getUserName(),u.getPassword(),u.getGender(),u.getBirthday(),u.getIcon(),u.getId());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}

	@Override
	public int deleteUser(int id) {
		String sql = "delete users where id  = ? ";
		int num = 0;
		try {
			num = qr.execute(sql, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}

	@Override
	public List<Users> findPage(int pageIndex, int pageSize) {
		int max = pageIndex * pageSize;
		int min = (pageIndex-1) * pageSize;
		String sql = "select * from "
				+ "(select rownum r,u.* from "
				+ "(select * from users order by id) u "
				+ "where rownum <= ? ) "
				+ "where r > ?";
		List<Users> list = null;
		
		try {
			list = qr.query(sql, new BeanListHandler<>(Users.class),max,min);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return list;
	}

	@Override
	public int totalCount() {
		int count = 0;
		String sql = "select count(1) from users";
		try {
			BigDecimal bg=  qr.query(sql, new ScalarHandler<BigDecimal>());
			count = bg.intValue();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public Users login(String username, String password) {
		String sql ="select * from users where username = ? and password = ?";
		Users u = null;
		try {
			u = qr.query(sql, new BeanHandler<>(Users.class),username,password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return u;
	}

}
